<?php

namespace App\Console\Commands;

use App\Models\Game\Center\AccountProfit;
use App\Models\Game\Center\Action;
use App\Models\Game\Center\AppSource;
use App\Models\Game\Center\AppSourceCpa;
use App\Models\Game\Center\PayOrder;
use App\Models\Game\Center\TaskTypeStat01;
use App\Models\Game\Center\ThirdExchangeRecord;
use App\Models\Game\Center\WithdrawOrder;
use App\Models\Mongo\DailyLog\ClientLogUnique;
use App\Models\Mongo\DailyLog\DayCpaCost;
use App\Models\Mongo\DailyLog\DayStat;
use App\Services\CalcService;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\DB;
use Jenssegers\Mongodb\Collection;

class CalcDayStat extends Command
{
    protected $signature = 'calc:day-stat {--day= : 请输入运行日期, 例如2021-01-01}';

    protected $description = '每日数据统计';

    /**
     * @var Carbon
     */
    protected $day;

    /**
     * @var Carbon
     */
    protected $dayStart;

    /**
     * @var Carbon
     */
    protected $dayEnd;

    public function __construct()
    {
        parent::__construct();
    }

    /**
     * @param Carbon $carbon
     * @return CalcDayStat
     */
    public function setDay(Carbon $carbon): CalcDayStat
    {
        $this->day = $carbon;
        $this->dayStart = $this->day->copy()->startOfDay();
        $this->dayEnd = $this->day->copy()->endOfDay();
        return $this;
    }

    /**
     * 运行时间与查询时间不是同一天, 视为最后一次查询
     * @return bool
     */
    private function isLastQuery(): bool
    {
        return $this->day->day != now()->day;
    }

    public function handle()
    {
        $this->day = $this->option('day') ? Carbon::createFromFormat('Y-m-d', $this->option('day')) : now()->subMinutes(2);
        // 前10分钟计算昨天
        if (!$this->option('day')) {
            if ($this->day->hour === 0 && $this->day->minute < 5) $this->day = now()->subDay();
        }

        $this->dayStart = $this->day->copy()->startOfDay();
        $this->dayEnd = $this->day->copy()->endOfDay();

        $cplCosts = $this->getCplCost();
        $cpaCosts = $this->getCpaCost();
        $cplTaskCosts = $this->getCplTaskCost();
        $registerCounts = $this->getRegisterCount();
        $withdrawSums = $this->getWithdrawSum();
        $paySums = $this->getPaySum();

        // 耗时查询 最后一次查
        if ($this->isLastQuery()) {
            $payAndWithdrawSumAll = $this->getPayAndWithdrawSumAll();
            $freeUserStocks = $this->getFreeUserStocks();
        }

        $withdrawFirsts = $this->getWithdrawFirst();
        $payFirsts = $this->getPayFirst();
        $payCnts = $this->getPayCnt();
        $payFirstOldUsers = $this->getPayFirstOldUser();
        $yesterdaySpreadInfos = $this->getYesterdaySpreadInfos();
        $authAllowUsers = $this->getAuthAllowUsers();
        $cpaCustoms = $this->getAppSourceCpaCustoms();

        $thirdExchangeSums = $this->getThirdExchangeSums();
        $thirdExchangeFirsts = $this->getThirdExchangeFirst();

        $loginUsers = CalcService::getLoginUsers([$this->day->format('Ymd'), $this->day->format('Ymd')]);
        $activeUsers = CalcService::getActiveUsers([$this->day->format('Ymd'), $this->day->format('Ymd')]);

        // 减少查询次数
        $dayStats = DayStat::where('day', $this->day->toDateString())->get()->keyBy('source');
        $appSources = AppSource::get();
        foreach ($appSources as $appSource) {
            $dayStat = $dayStats[$appSource->id] ?? new DayStat(['day' => $this->day->toDateString(), 'source' => $appSource->id]);

            list($dayStat->pay, $dayStat->payUser, $dayStat->payAlipay, $dayStat->payWechat) = $paySums[$appSource->id] ?? [0, 0, 0, 0]; // 今日充值金额和人数
            list($dayStat->payFirst, $dayStat->payFirstUser) = $payFirsts[$appSource->id] ?? [0, 0]; // 今日首次充值金额和人数
            list($dayStat->withdraw, $dayStat->withdrawUser, $dayStat->withdrawCash, $dayStat->withdrawJd, $dayStat->withdrawChannelFee) = $withdrawSums[$appSource->id] ?? [0,0,0,0,0]; // 今日权益金额和人数
            list($dayStat->withdrawFirst, $dayStat->withdrawFirstUser) = $withdrawFirsts[$appSource->id] ?? [0, 0];// 今日首次权益金额和人数
            list($dayStat->paySum, $dayStat->withdrawSum) = $payAndWithdrawSumAll[$appSource->id] ?? [0, 0]; // 截止今日累计充值 / 截止今日累计权益
            list($dayStat->payCntAlipayFailed, $dayStat->payCntAlipaySuccess, $dayStat->payCntWechatFailed, $dayStat->payCntWechatSuccess) = $payCnts[$appSource->id] ?? [0, 0, 0, 0];// 支付宝和微信未支付次数和支付次数
            list($dayStat->payFirstOldUser24, $dayStat->payFirstOldUser48) = $payFirstOldUsers[$appSource->id] ?? [0, 0];// 24小时内首次支付 和 48小时内首次支付
            list($dayStat->thirdExchange, $dayStat->thirdExchangeUser) = $thirdExchangeSums[$appSource->id] ?? [0, 0];
            list($dayStat->thirdExchangeFirst, $dayStat->thirdExchangeFirstUser) = $thirdExchangeFirsts[$appSource->id] ?? [0, 0];

            $dayStat->registerUser = $registerCounts[$appSource->id] ?? 0; // 注册人数
            $dayStat->loginUser = $loginUsers[$appSource->id]??0; // 登录人数
            $dayStat->activeUser = $activeUsers[$appSource->id]??0; // 游戏人数
            $dayStat->cplCost = $cplCosts[$appSource->id] ?? 0;// Cpl支出
            $dayStat->cplTaskCost = $cplTaskCosts[$appSource->id] ?? 0;// Cpl场内支出

            // CPA花费 1.接口查询得到 2.自定义公式 3.手工输入
            if (isset($cpaCosts[$appSource->id])) {
                $dayStat->cpaCost = $cpaCosts[$appSource->id];
            } else if (isset($cpaCustoms[$appSource->id])) {
                list($dayStat->cpaCost, $dayStat->cpaCostFormula) = $this->calcAppSourceCpaCustomCost($cpaCustoms[$appSource->id], $dayStat);
            } else {
                $dayStat->cpaCost = $dayStat->cpaCost ?: 0;
            }

            list($dayStat->freeUserStockBullet, $dayStat->freeUserStockHb) = $freeUserStocks[$appSource->id] ?? [0, 0];
            $dayStat->authAllowUser = $authAllowUsers[$appSource->id] ?? 0;// 同意授权人数

            // 未填写推广信息时尝试使用昨日填写
            if (!$dayStat->spreadChannel && !$dayStat->spreadType && !$dayStat->spreadUser) {
                $dayStat->spreadChannel = $yesterdaySpreadInfos[$appSource->id]->spreadChannel ?? '';
                $dayStat->spreadType = $yesterdaySpreadInfos[$appSource->id]->spreadType ?? '';
                $dayStat->spreadUser = $yesterdaySpreadInfos[$appSource->id]->spreadUser ?? '';
            }
            $dayStat->save();
        }

        $this->storeAllSource();

        // 最后一次计算月统计/渠道LTV
        if ($this->isLastQuery()) {
            Artisan::call(CalcMonthStat::class, [
                '--day' => $this->day->toDateString(),
            ]);
            Artisan::call(CalcSourceLtv::class, [
                '--day' => $this->day->toDateString(),
            ]);
        }
    }

    /**
     * 对存储的数据进行汇总
     */
    public function storeAllSource()
    {
        $dayStat = new DayStat();
        $dateString = $this->day->toDateString();
        $sumData = $dayStat->getSumDataByDay($dateString);
        $sumData['payUser'] = PayOrder::whereBetween('payed_at', ["{$dateString} 00:00:00", "{$dateString} 23:59:59"])->distinct('player_id')->count();
        $sumData['maxOnline'] = CalcService::getMaxOnlineInDay($this->day->format('Ymd'));

        // 今日消费人数 rmb+点券
        $payAllUser = DB::connection("gameCenter")->select("
            SELECT count(DISTINCT(player_id)) as payAllUser2 FROM
            (
                SELECT player_id FROM tb_pay_order WHERE payed_at BETWEEN '{$dateString} 00:00:00' AND '{$dateString} 23:59:59'
                UNION
                SELECT player_id FROM tb_cost_order WHERE completed_at BETWEEN '{$dateString} 00:00:00' AND '{$dateString} 23:59:59'
            ) p
        ");
        $sumData['payUser2'] = $payAllUser[0] ? ($payAllUser[0]->payAllUser2 ?? 0) : 0;
        $dayStat->updateOrCreate(['day' => $dateString, 'source' => 0], $sumData);
    }

    /**
     * 累计充值 / 权益
     * @return array
     */
    private function getPayAndWithdrawSumAll(): array
    {
        $lists = AccountProfit::selectRaw('source, IFNULL(SUM(sum_pay), 0) AS sum_pay, IFNULL(SUM(sum_withdraw), 0) AS sum_withdraw')
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        foreach ($lists as $source => $v) {
            $lists[$source] = [$v['sum_pay'] ?? 0, $v['sum_withdraw'] ?? 0];
        }
        return $lists;
    }

    /**
     * 今日充值数据
     * @return array
     */
    private function getPaySum(): array
    {
        $timeRange = [$this->dayStart->toDateTimeString(), $this->dayEnd->toDateTimeString()];
        // 渠道 支付方式 金额
        $lists = PayOrder::ofDatetimeRange('payed_at', $timeRange)
            ->selectRaw('source, channel, gateway, SUM(price) AS price')
            ->groupBy('source', 'channel', 'gateway')
            ->get()
            ->groupBy('source');

        // 渠道 人数
        $lists2 = PayOrder::ofDatetimeRange('payed_at', $timeRange)
            ->selectRaw('source, COUNT(DISTINCT player_id) AS cnt')
            ->groupBy('source')
            ->get()
            ->groupBy('source');

        $data = [];
        foreach ($lists as $source => $v) {
            // 支付通道有很多, alipay wechat 以及第三方支付, 第三方支付的gateway为alipay/wechat的要统计到一起
            $data[$source] = [
                $v->sum('price'),
                $lists2[$source]->sum('cnt') ?? 0, // 人数单独获取
                $v->where('channel', 'alipay')->sum('price') + $v->where('gateway', 'alipay')->sum('price'),
                $v->where('channel', 'wechat')->sum('price') + $v->where('gateway', 'wechat')->sum('price'),
            ];
        }
        return $data;
    }

    /**
     * 首日充值数据
     * @return array []int
     */
    private function getPayFirst(): array
    {
        $timeRange = [$this->dayStart->toDateTimeString(), $this->dayEnd->toDateTimeString()];
        $lists = PayOrder::selectRaw("source, SUM(price) AS price, COUNT(DISTINCT player_id) AS cnt")
            ->where('status', PayOrder::STATUS_PAYED)
            ->whereBetween('payed_at', $timeRange)
            ->whereBetween('register_at', $timeRange)
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();

        foreach ($lists as $source => $v) {
            $lists[$source] = [$v['price'] ?? 0, $v['cnt'] ?? 0];
        }
        return $lists;
    }

    /**
     * 支付和未支付次数
     * @return array
     */
    private function getPayCnt(): array
    {
        $timeRange = [$this->dayStart->toDateTimeString(), $this->dayEnd->toDateTimeString()];
        $lists = PayOrder::selectRaw("source, channel, gateway, SUM(CASE status WHEN 0 THEN 1 ELSE 0 END) failed, SUM(CASE status WHEN 1 THEN 1 ELSE 0 END) success")
            ->whereBetween('updated_at', $timeRange)
            ->groupBy('source', 'channel', 'gateway')
            ->get()
            ->groupBy('source');

        $data = [];
        foreach ($lists as $source => $v) {
            $data[$source] = [
                $v->where('channel', 'alipay')->sum('failed') + $v->where('gateway', 'alipay')->sum('failed'),
                $v->where('channel', 'alipay')->sum('success') + $v->where('gateway', 'alipay')->sum('success'),

                $v->where('channel', 'wechat')->sum('failed') + $v->where('gateway', 'wechat')->sum('failed'),
                $v->where('channel', 'wechat')->sum('success') + $v->where('gateway', 'wechat')->sum('success'),
            ];
        }
        return $data;
    }

    /**
     * 今日权益数据
     * @return int[]
     */
    public function getWithdrawSum(): array
    {
        $timeRange = [$this->dayStart->toDateTimeString(), $this->dayEnd->toDateTimeString()];
        $data = WithdrawOrder::ofDatetimeRange('updated_at', $timeRange)
            ->whereStatus(WithdrawOrder::STATUS_SUCCESS)
            ->selectRaw('source, channel, SUM(money) AS money, COUNT(DISTINCT player_id) AS cnt')
            ->groupBy('source', 'channel')
            ->get()
            ->groupBy('source');

        $lists = [];
        foreach ($data as $source => $v) {
            $sum = $v->sum('money');
            $user = $v->sum('cnt');

            $v = $v->keyBy('channel');
            $jdSum = (int) ($v['jd']['money'] ?? 0);
            $cashSum = $sum - ($v['jd']['money'] ?? 0);
            $channelFeeSum = ($v['yajie']['money'] ?? 0) * config('game.withdraw_channel_fee_yajie');
            $lists[$source] = [$sum, $user, $cashSum, $jdSum, $channelFeeSum];
        }
        return $lists;
    }

    /**
     * 首日权益 数据
     * @return array
     */
    private function getWithdrawFirst(): array
    {
        $timeRange = [$this->dayStart->toDateTimeString(), $this->dayEnd->toDateTimeString()];
        $lists = WithdrawOrder::selectRaw("source, SUM(money) AS money, COUNT(DISTINCT player_id) AS cnt")
            ->where('status', WithdrawOrder::STATUS_SUCCESS)
            ->whereBetween('updated_at', $timeRange)
            ->whereBetween('register_at', $timeRange)
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        foreach ($lists as $source => $v) {
            $lists[$source] = [$v['money'] ?? 0, $v['cnt'] ?? 0];
        }
        return $lists;
    }

    /**
     * 今日注册数量
     * @return array
     */
    private function getRegisterCount(): array
    {
        return Action::where('day', $this->day->format('Ymd'))
            ->whereType(Action::TYPE_REGISTER)
            ->selectRaw('source, COUNT(DISTINCT user_id) AS cnt')
            ->groupBy('source')
            ->get()
            ->pluck('cnt', 'source')
            ->all();
    }

    /**
     * 前两天注册, 今天首次充值, 间隔时间在24小时内和48小时内的用户
     * @return array
     */
    private function getPayFirstOldUser(): array
    {
        $todayStart = $this->dayStart->toDateTimeString();
        $todayEnd = $this->dayEnd->toDateTimeString();

        $threeDayStart = $this->dayStart->copy()->subDays(2);

        $sql = "SELECT source, SUM(CASE WHEN hours <= 24 THEN 1 ELSE 0 END) hour24, SUM(CASE WHEN hours <= 48 THEN 1 ELSE 0 END) hour48
                FROM (
                    SELECT source, player_id, register_at, payed_at, TIMESTAMPDIFF(HOUR, register_at, MIN(payed_at)) hours
                    FROM `tb_pay_order`
                    WHERE payed_at BETWEEN '{$threeDayStart}' AND '{$todayEnd}'
                    AND register_at BETWEEN '{$threeDayStart}' AND '{$todayEnd}'
                    AND status = 1
                    GROUP BY player_id
                    HAVING payed_at BETWEEN '{$todayStart}' AND '{$todayEnd}'
                ) t
                GROUP BY source;";
        $items = DB::connection('gameCenter')->select($sql);
        $all = [];
        foreach ($items as $k => $item) {
            $all[$item->source] = [$item->hour24, $item->hour48];
        }
        return $all;
    }

    /**
     * CPL支出费用
     * @return array
     */
    public function getCplCost(): array
    {
        $dayStart = $this->dayStart->toDateTimeString();
        $dayEnd = $this->dayEnd->toDateTimeString();

        $where = "WHERE log.created_at BETWEEN '{$dayStart}' AND '{$dayEnd}'";

        $sql = "SELECT SUM(value * player) AS cost, source FROM (
                    SELECT config.value, COUNT(log.player_id) AS player, info.source FROM `tb_cpl_issue_config` AS config

                    LEFT JOIN tb_cpl_issue_user_log AS log
                    ON config.id = log.cpl_issue_config_id

                    LEFT JOIN tb_cpl_activity_issue AS info
                    ON config.cpl_activity_issue_id = info.id

                    {$where}
                    GROUP BY config.id
                ) AS tmp
                GROUP BY source";
        $costs = DB::connection('gameCenter')->select($sql);
        $all = [];
        foreach ($costs as $k => $cost) {
            $all[$cost->source] = (int) $cost->cost * 100;// 转换为分
        }
        return $all;
    }

    /**
     * 场内CPL消费
     * @return array
     */
    public function getCplTaskCost(): array
    {
        // TODO 待补充
        // return TaskTypeStat01::where('day', $this->dayStart->toDateString())
        //     ->selectRaw('SUM(cost) AS cost, source')
        //     ->groupBy('source')
        //     ->get()
        //     ->pluck('cost', 'source')
        //     ->all();
        return [];
    }

    /**
     * 获取CPA花费 四舍五入取整
     * @return array
     */
    public function getCpaCost(): array
    {
        $dayCpaCost = new DayCpaCost();
        $cpaCosts = $dayCpaCost->getCpaCostActuals([$this->dayStart->toDateString(), $this->dayEnd->toDateString()]);
        return array_map(function ($v) {
            $v /= 100;
            return round($v) * 100;
        }, $cpaCosts);
    }

    /**
     * 渠道自定义CPA
     * @return AppSourceCpa|Builder|\Illuminate\Database\Eloquent\Collection
     */
    public function getAppSourceCpaCustoms()
    {
        return AppSourceCpa::whereChannel(AppSourceCpa::CHANNEL_CUSTOM)->get()->keyBy('source');
    }

    /**
     * 自定义公式得出的推广费
     * @param AppSourceCpa $appSourceCpa
     * @param DayStat $dayStat
     * @return array
     */
    public function calcAppSourceCpaCustomCost(AppSourceCpa $appSourceCpa, DayStat $dayStat)
    {
        if (!$appSourceCpa->formula) return [0, ''];
        // 原withdraw变成了thirdExchange, 这里要处理币值问题
        // $formula = str_replace('$withdraw', '($thirdExchange/'.(getCurrencyHbScale(true)).')', $appSourceCpa->formula);
        $formula = str_replace('$', '$dayStat->', $appSourceCpa->formula);
        eval("\$cost = {$formula};");
        $cost = $cost?:0;
        if ($cost < 0) $cost = 100;
        return [$cost, $appSourceCpa->formula_text];
    }

    /**
     * 免费用户库存
     * @return int[]
     */
    public function getFreeUserStocks(): array
     {
         $sql = "SELECT a.source, IFNULL(SUM(p.bullet), 0) AS bullet, IFNULL(SUM(p.hbticket), 0) AS hbticket FROM `tb_account` AS a
LEFT JOIN `tb_account_profit` AS p
ON a.playerId = p.playerId
WHERE a.created_at >= '{$this->dayStart->toDateTimeString()}'
AND a.created_at <= '{$this->dayEnd->toDateTimeString()}'
AND p.sum_pay = 0
GROUP BY source";
         $res = DB::connection('gameCenter')->select($sql);
         $lists = [];
         foreach ($res as $k => $v) {
             $v = (array) $v;
             $lists[$v['source']] = [$v['bullet'], $v['hbticket']];
         }
         return $lists;
     }

    /**
     * 获取推广信息
     */
    public function getYesterdaySpreadInfos()
     {
         $yesterday = $this->day->copy()->subDay()->toDateString();
         $lists = DayStat::where('day', $yesterday)
             ->get([
                 'source',
                 'spreadChannel',
                 'spreadType',
                 'spreadUser'
             ]);

         return $lists->keyBy('source');
     }

    /**
     * 同意用户授权用户数量
     * @return array
     */
    public function getAuthAllowUsers(): array
    {
        $aggregate = [
            [
                '$match' => [
                    'action_id' => ClientLogUnique::ACTION_ALLOW,
                    'day' => $this->day->toDateString(),
                ],
            ],
            [
                '$group' => [
                    '_id' => '$source',
                    'cnt' => ['$sum' => 1,],
                ],
            ],
        ];
        $collects = ClientLogUnique::query()->raw(function (Collection $collection) use ($aggregate) {
            return $collection->aggregate($aggregate);
        });
        return $collects->pluck('cnt', '_id')->toArray();
     }
    /**
     * 三方商城
     * @return array
     */
    public function getThirdExchangeSums(): array
    {
        $timeRange = [$this->dayStart->toDateTimeString(), $this->dayEnd->toDateTimeString()];
        $lists = ThirdExchangeRecord::selectRaw('source, SUM(cost) cost, COUNT(DISTINCT player_id) cnt')
            ->whereBetween('created_at', $timeRange)
            ->where('status', ThirdExchangeRecord::STATUS_SUCCESS)
            ->groupBy('source')
            ->get()
            ->keyBy('source');
        $data = [];
        foreach ($lists as $source => $v) {
            $data[$source] = [$v['cost'] ?? 0, $v['cnt'] ?? 0];
        }
        return $data;
    }

    /**
     * 首日三方商城
     * @return array
     */
    public function getThirdExchangeFirst(): array
    {
        $timeRange = [$this->dayStart->toDateTimeString(), $this->dayEnd->toDateTimeString()];
        $lists = ThirdExchangeRecord::selectRaw("source, SUM(cost) cost, COUNT(DISTINCT player_id) AS cnt")
            ->where('status', ThirdExchangeRecord::STATUS_SUCCESS)
            ->whereBetween('created_at', $timeRange)
            ->whereBetween('register_at', $timeRange)
            ->groupBy('source')
            ->get()
            ->keyBy('source')
            ->toArray();
        foreach ($lists as $source => $v) {
            $lists[$source] = [$v['cost'] ?? 0, $v['cnt'] ?? 0];
        }
        return $lists;
    }
}
